In this example, TEXTJOIN is combined with a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. Dynamic arrays are available in Microsoft 365 plans, Excel for the web, and Excel mobile apps.

The Sales sheet has 2 years of sales data, formatted as an Excel table. The columns are named ranges - YrCol, RegCol, CatCol, NameCol and QtyCol.

On the Targets sheet, there is another table, with a sales target number for each year and category.

In column E, a TEXTJOIN formula creates a list of sales reps who met the sales target, for that row's year and category.

The TEXTJOIN function combines the results from a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. To show how those functions work, there's a demo on the Report sheet.

There are drop down lists at the top of the sheet, where you can select a year and category. A SUMIFS formula returns the sales target amount for the selected year and category.

Cell B4 is named YrSel, cell C4 is named CatSel, and cell D4 is named TgtSel.

This dynamic array formula is in cell C7, and the results spill down to the cells below, if necessary.

=IFERROR(UNIQUE(SORT(FILTER(NameCol, (YrCol=YrSel) *(CatCol=CatSel) *(QtyCol>=TgtSel)))), "--")

In this formula, the FILTER function returns the rep names from the Name column in the Sales table, if all 3 criteria are met:

Then, the SORT function puts those names in alphabetical order, and the UNIQUE function removes any duplicate names.

Finally, the IFERROR returns two dashes ("--"), if there is an error in returning the sales rep names. For example, no sales reps reached the target amount for the selected year and category.

On the Targets sheet, a similar formulas is used to create a list of sales reps. This is the function in cell E4 on the Targets sheet:

=IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(SORT(FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))))), "--")

The TEXTJOIN function combines all the names, separated by a comma and space character, so the results are shown in a single cell, instead of spilling down a column.